package com.abc.recycling.repository;

import com.abc.recycling.model.Order;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;
import java.util.Map;

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {

    @Query("SELECT COUNT(o) FROM Order o WHERE DATE(o.createTime) = DATE(:date)")
    long countOrdersByDate(LocalDateTime date);
    
    @Query("SELECT COUNT(DISTINCT o.userId) FROM Order o")
    long countActiveUsers();
    
    @Query("SELECT COALESCE(SUM(o.actualWeight), 0) FROM Order o WHERE o.status = 'COMPLETED'")
    double sumTotalWeight();
    
    @Query("SELECT COALESCE(SUM(o.price), 0) FROM Order o")
    double getTotalIncome();
    
    List<Order> findByCollectorId(Long collectorId);
    
    Page<Order> findByCollectorId(Long collectorId, Pageable pageable);
    
    List<Order> findByUserId(Long userId);
    
    Page<Order> findByUserId(Long userId, Pageable pageable);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.status = :status")
    long countByStatus(@Param("status") String status);
    
    @Query("SELECT o FROM Order o ORDER BY o.createTime DESC")
    List<Order> findTopNByOrderByCreateTimeDesc(Pageable pageable);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.createTime BETWEEN :startDate AND :endDate")
    long countByCreateTimeBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT COALESCE(SUM(o.actualWeight), 0) FROM Order o WHERE o.createTime BETWEEN :startDate AND :endDate")
    double sumWeightByDateRange(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT COALESCE(SUM(o.price), 0) FROM Order o WHERE o.createTime BETWEEN :startDate AND :endDate")
    double sumIncomeByDateRange(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
           "COUNT(*) as count " +
           "FROM recycling_order " +
           "WHERE collector_id = :collectorId " +
           "AND create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
           "ORDER BY date", nativeQuery = true)
    List<Map<String, Object>> getOrderTrendByCollector(@Param("collectorId") Long collectorId, 
            @Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
    
    @Query(value = "SELECT waste_category_id as type, COUNT(*) as count " +
           "FROM recycling_order WHERE collector_id = :collectorId " +
           "GROUP BY waste_category_id", nativeQuery = true)
    List<Map<String, Object>> getItemDistributionByCollector(@Param("collectorId") Long collectorId);
    
    List<Order> findByUserIdOrderByCreateTimeDesc(Long userId);
    
    @Query("SELECT o FROM Order o WHERE o.status = :status")
    Page<Order> findByStatus(@Param("status") String status, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.orderNo LIKE %:keyword% OR o.address LIKE %:keyword%")
    Page<Order> findByKeyword(@Param("keyword") String keyword, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.status = :status AND (o.orderNo LIKE %:keyword% OR o.address LIKE %:keyword%)")
    Page<Order> findByStatusAndKeyword(@Param("status") String status, @Param("keyword") String keyword, Pageable pageable);
    
    long countByCreateTimeAfter(LocalDateTime date);
    
    @Query("SELECT AVG(o.rating) FROM Order o WHERE o.collectorId = :collectorId AND o.rating IS NOT NULL")
    Double getAverageRatingByCollectorId(@Param("collectorId") Long collectorId);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.collectorId = :collectorId AND o.rating IS NOT NULL")
    Long countRatedOrdersByCollectorId(@Param("collectorId") Long collectorId);
    
    @Query("SELECT NEW map(FUNCTION('DATE_FORMAT', o.createTime, '%Y-%m') as month, COUNT(o) as count) " +
           "FROM Order o " +
           "WHERE o.createTime >= :startDate " +
           "GROUP BY FUNCTION('DATE_FORMAT', o.createTime, '%Y-%m') " +
           "ORDER BY month")
    List<Map<String, Object>> getOrderTrend(@Param("startDate") LocalDateTime startDate);
    
    @Query("SELECT NEW map(o.itemType as type, COUNT(o) as count) " +
           "FROM Order o " +
           "WHERE o.status = 'COMPLETED' " +
           "GROUP BY o.itemType")
    List<Map<String, Object>> getItemDistribution();
    
    @Query("SELECT NEW map(o.itemType as type, COUNT(o) as count) " +
           "FROM Order o " +
           "WHERE o.status = 'COMPLETED' " +
           "AND o.createTime BETWEEN :startDate AND :endDate " +
           "GROUP BY o.itemType")
    List<Map<String, Object>> getItemDistributionByDateRange(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
    
    @Query("SELECT NEW map(FUNCTION('DATE_FORMAT', o.createTime, '%Y-%m-%d') as date, " +
           "COUNT(o) as count) " +
           "FROM Order o " +
           "WHERE o.createTime BETWEEN :startDate AND :endDate " +
           "GROUP BY FUNCTION('DATE_FORMAT', o.createTime, '%Y-%m-%d') " +
           "ORDER BY date")
    List<Map<String, Object>> getMonthlyOrderCount(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
    
    List<Order> findByCollectorIdAndStatus(Long collectorId, String status);
    
    Page<Order> findByCollectorIdAndStatus(Long collectorId, String status, Pageable pageable);
    
    long countByCollectorId(Long collectorId);
    
    long countByCollectorIdAndStatus(Long collectorId, String status);
    
    @Query("SELECT COALESCE(SUM(o.price), 0) FROM Order o WHERE o.collectorId = :collectorId AND o.createTime > :date")
    double sumPriceByCollectorIdAndCreateTimeAfter(@Param("collectorId") Long collectorId, @Param("date") LocalDateTime date);
    
    @Query("SELECT COALESCE(SUM(o.actualWeight), 0) FROM Order o WHERE o.collectorId = :collectorId")
    double sumWeightByCollectorId(@Param("collectorId") Long collectorId);
    
    @Query("SELECT o FROM Order o WHERE o.collectorId = :collectorId ORDER BY o.createTime DESC")
    Page<Order> findByCollectorIdOrderByCreateTimeDesc(Long collectorId, Pageable pageable);
    
    long countByCollectorIdAndCreateTimeAfter(Long collectorId, LocalDateTime date);
    
    @Query("SELECT COALESCE(SUM(o.actualWeight), 0) FROM Order o WHERE o.status = :status")
    double sumWeightByStatus(@Param("status") String status);
    
    @Query("SELECT AVG(o.rating) FROM Order o WHERE o.rating IS NOT NULL")
    double averageRating();
    
    long countByUserId(Long userId);
    
    @Query("SELECT COALESCE(SUM(o.actualWeight), 0) FROM Order o WHERE o.userId = :userId")
    double sumWeightByUserId(@Param("userId") Long userId);
    
    @Query("SELECT AVG(o.rating) FROM Order o WHERE o.userId = :userId AND o.rating IS NOT NULL")
    Double averageRatingByUserId(@Param("userId") Long userId);
    
    @Query("SELECT COALESCE(SUM(o.price), 0) FROM Order o WHERE o.userId = :userId")
    double sumIncomeByUserId(@Param("userId") Long userId);
    
    @Query("SELECT o FROM Order o WHERE o.orderNo LIKE %:orderNo%")
    Page<Order> findByOrderNoContaining(@Param("orderNo") String orderNo, Pageable pageable);
    
    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.orderNo LIKE %:orderNo%")
    Page<Order> findByStatusAndOrderNoContaining(@Param("status") String status, @Param("orderNo") String orderNo, Pageable pageable);
    
    @Query(value = "SELECT o.address as region, COUNT(*) as count, " +
           "COALESCE(SUM(o.actual_weight), 0) as totalWeight, COALESCE(SUM(o.price), 0) as totalIncome " +
           "FROM recycling_order o " +
           "WHERE o.create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY o.address", nativeQuery = true)
    List<Map<String, Object>> getRegionStatistics(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
            
    @Query(value = "SELECT o.address as region, o.status as status, COUNT(*) as count " +
           "FROM recycling_order o " +
           "WHERE o.address = :region " +
           "AND o.create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY o.address, o.status", nativeQuery = true)
    List<Map<String, Object>> getRegionDetail(@Param("region") String region,
            @Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
            
    @Query(value = "SELECT DATE_FORMAT(o.create_time, '%Y-%m-%d') as date, " +
           "COUNT(*) as count " +
           "FROM recycling_order o " +
           "WHERE o.address = :region " +
           "AND o.create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY DATE_FORMAT(o.create_time, '%Y-%m-%d') " +
           "ORDER BY date", nativeQuery = true)
    List<Map<String, Object>> getRegionTrend(@Param("region") String region,
            @Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
            
    @Query("SELECT o FROM Order o WHERE o.createTime BETWEEN :startDate AND :endDate")
    List<Order> findByCreateTimeBetween(@Param("startDate") LocalDateTime startDate, @Param("endDate") LocalDateTime endDate);
    
    @Query(value = "SELECT " +
           "COUNT(*) as total_orders, " +
           "COUNT(CASE WHEN status = 'COMPLETED' THEN 1 END) as completed_orders, " +
           "COUNT(CASE WHEN status = 'PROCESSING' THEN 1 END) as processing_orders, " +
           "COUNT(CASE WHEN status = 'PENDING' THEN 1 END) as pending_orders, " +
           "COUNT(CASE WHEN create_time >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') " +
           "AND create_time < DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m-01') " +
           "THEN 1 END) as monthly_orders, " +
           "COUNT(CASE WHEN status = 'COMPLETED' " +
           "AND create_time >= DATE_FORMAT(CURRENT_DATE, '%Y-%m-01') " +
           "AND create_time < DATE_FORMAT(DATE_ADD(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m-01') " +
           "THEN 1 END) as monthly_completed_orders " +
           "FROM recycling_order WHERE collector_id = :collectorId", nativeQuery = true)
    Map<String, Object> getCollectorOrderStats(@Param("collectorId") Long collectorId);
    
    @Query(value = "SELECT DATE_FORMAT(create_time, '%Y-%m-%d') as date, " +
           "COUNT(*) as orderCount, " +
           "SUM(actual_weight) as totalWeight, " +
           "SUM(price) as totalIncome, " +
           "AVG(rating) as averageRating " +
           "FROM recycling_order " +
           "WHERE create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d') " +
           "ORDER BY date", nativeQuery = true)
    List<Map<String, Object>> getStatisticsReport(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate);
            
    @Query(value = "SELECT o.*, COUNT(*) as order_count " +
           "FROM recycling_order o " +
           "WHERE o.create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY o.collector_id " +
           "ORDER BY order_count DESC " +
           "LIMIT :limit", nativeQuery = true)
    List<Map<String, Object>> getTopCollectors(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate, 
            @Param("limit") int limit);
            
    @Query(value = "SELECT o.*, COUNT(*) as order_count " +
           "FROM recycling_order o " +
           "WHERE o.create_time BETWEEN :startDate AND :endDate " +
           "GROUP BY o.station_id " +
           "ORDER BY order_count DESC " +
           "LIMIT :limit", nativeQuery = true)
    List<Map<String, Object>> getTopStations(@Param("startDate") LocalDateTime startDate, 
            @Param("endDate") LocalDateTime endDate, 
            @Param("limit") int limit);
    
    @Query("SELECT COUNT(o) FROM Order o WHERE o.collectorId = :collectorId AND o.status = :status AND o.createTime > :date")
    long countByCollectorIdAndStatusAndCreateTimeAfter(@Param("collectorId") Long collectorId, @Param("status") String status, @Param("date") LocalDateTime date);
}